package com.jza04.flightbooking.mapper;

import com.jza04.flightbooking.dto.FlightRevenueDTO;
import com.jza04.flightbooking.entity.Ticket;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 购票Mapper接口
 */
@Mapper
public interface TicketMapper {
    
    /**
     * 查询所有购票记录
     */
    @Select("SELECT * FROM ticket_JZA04")
    @Results({
        @Result(property = "ticketId", column = "ticket_id"),
        @Result(property = "flightId", column = "flight_id"),
        @Result(property = "passengerId", column = "passenger_id"),
        @Result(property = "seatNumber", column = "seat_number"),
        @Result(property = "purchaseDate", column = "purchase_date"),
        @Result(property = "actualPrice", column = "actual_price"),
        @Result(property = "status", column = "status"),
        @Result(property = "discountId", column = "discount_id"),
        @Result(property = "flight", column = "flight_id", 
                one = @One(select = "com.jza04.flightbooking.mapper.FlightMapper.findById")),
        @Result(property = "passenger", column = "passenger_id", 
                one = @One(select = "com.jza04.flightbooking.mapper.PassengerMapper.findById")),
        @Result(property = "discount", column = "discount_id", 
                one = @One(select = "com.jza04.flightbooking.mapper.DiscountMapper.findById"))
    })
    List<Ticket> findAll();
    
    /**
     * 根据ID查询购票记录
     */
    @Select("SELECT * FROM ticket_JZA04 WHERE ticket_id = #{ticketId}")
    @Results({
        @Result(property = "ticketId", column = "ticket_id"),
        @Result(property = "flightId", column = "flight_id"),
        @Result(property = "passengerId", column = "passenger_id"),
        @Result(property = "seatNumber", column = "seat_number"),
        @Result(property = "purchaseDate", column = "purchase_date"),
        @Result(property = "actualPrice", column = "actual_price"),
        @Result(property = "status", column = "status"),
        @Result(property = "discountId", column = "discount_id"),
        @Result(property = "flight", column = "flight_id", 
                one = @One(select = "com.jza04.flightbooking.mapper.FlightMapper.findById")),
        @Result(property = "passenger", column = "passenger_id", 
                one = @One(select = "com.jza04.flightbooking.mapper.PassengerMapper.findById")),
        @Result(property = "discount", column = "discount_id", 
                one = @One(select = "com.jza04.flightbooking.mapper.DiscountMapper.findById"))
    })
    Ticket findById(Integer ticketId);
    
    /**
     * 根据航班ID查询购票记录
     */
    @Select("SELECT * FROM ticket_JZA04 WHERE flight_id = #{flightId}")
    @Results({
        @Result(property = "ticketId", column = "ticket_id"),
        @Result(property = "flightId", column = "flight_id"),
        @Result(property = "passengerId", column = "passenger_id"),
        @Result(property = "seatNumber", column = "seat_number"),
        @Result(property = "purchaseDate", column = "purchase_date"),
        @Result(property = "actualPrice", column = "actual_price"),
        @Result(property = "status", column = "status"),
        @Result(property = "discountId", column = "discount_id"),
        @Result(property = "passenger", column = "passenger_id", 
                one = @One(select = "com.jza04.flightbooking.mapper.PassengerMapper.findById"))
    })
    List<Ticket> findByFlightId(String flightId);
    
    /**
     * 根据旅客ID查询购票记录
     */
    @Select("SELECT * FROM ticket_JZA04 WHERE passenger_id = #{passengerId}")
    @Results({
        @Result(property = "ticketId", column = "ticket_id"),
        @Result(property = "flightId", column = "flight_id"),
        @Result(property = "passengerId", column = "passenger_id"),
        @Result(property = "seatNumber", column = "seat_number"),
        @Result(property = "purchaseDate", column = "purchase_date"),
        @Result(property = "actualPrice", column = "actual_price"),
        @Result(property = "status", column = "status"),
        @Result(property = "discountId", column = "discount_id"),
        @Result(property = "flight", column = "flight_id", 
                one = @One(select = "com.jza04.flightbooking.mapper.FlightMapper.findById"))
    })
    List<Ticket> findByPassengerId(Integer passengerId);
    
    /**
     * 添加购票记录
     */
    @Insert("INSERT INTO ticket_JZA04(flight_id, passenger_id, seat_number, purchase_date, actual_price, status, discount_id) " +
            "VALUES(#{flightId}, #{passengerId}, #{seatNumber}, #{purchaseDate}, #{actualPrice}, #{status}, #{discountId})")
    @Options(useGeneratedKeys = true, keyProperty = "ticketId")
    int insert(Ticket ticket);
    
    /**
     * 更新购票记录
     */
    @Update("UPDATE ticket_JZA04 SET seat_number = #{seatNumber}, actual_price = #{actualPrice}, " +
            "status = #{status}, discount_id = #{discountId} " +
            "WHERE ticket_id = #{ticketId}")
    int update(Ticket ticket);
    
    /**
     * 删除购票记录
     */
    @Delete("DELETE FROM ticket_JZA04 WHERE ticket_id = #{ticketId}")
    int delete(Integer ticketId);
    
    /**
     * 更新票状态
     */
    @Update("UPDATE ticket_JZA04 SET status = #{status} WHERE ticket_id = #{ticketId}")
    int updateStatus(Integer ticketId, String status);
    
    /**
     * 计算总收入
     */
    @Select("SELECT SUM(actual_price) FROM ticket_JZA04 WHERE status != 'CANCELLED'")
    BigDecimal calculateTotalRevenue();
    
    /**
     * 计算指定时间范围内的总收入
     */
    @Select("SELECT SUM(actual_price) FROM ticket_JZA04 WHERE status != 'CANCELLED' AND purchase_date BETWEEN #{startDate} AND #{endDate}")
    BigDecimal calculateRevenueByDateRange(Date startDate, Date endDate);
    
    /**
     * 按航班统计收入
     */
    @Select("SELECT flight_id, SUM(actual_price) as revenue FROM ticket_JZA04 WHERE status != 'CANCELLED' GROUP BY flight_id")
    @Results({
        @Result(property = "flightId", column = "flight_id"),
        @Result(property = "revenue", column = "revenue")
    })
    List<FlightRevenueDTO> calculateRevenueByFlight();
} 